﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace MJHDataService
{
    public class SqlDataService :IDisposable
    {
        private bool disposed = false;
        private SqlConnection oConn;

        public SqlDataService(string connectionStr)
        {
            oConn = new SqlConnection(connectionStr);
        }
        public DataSet ExecuteProcedureReturnDS(string sProcName, IDictionary parameterCollection)
        {
            return ExecuteProcedureReturnDS(sProcName, parameterCollection, 0);
        }
        public DataSet ExecuteProcedureReturnDS(string sProcName, IDictionary parameterCollection, int timeOut)
        {
            DataSet ds = null;
            try
            {
                oConn.Open();
                using (SqlCommand cmd = BuildSqlCommand(sProcName, parameterCollection, timeOut))
                {
                    using (SqlDataAdapter oAdapter = new SqlDataAdapter(cmd))
                    {
                        ds = new DataSet(sProcName);
                        oAdapter.Fill(ds);
                    }
                }
                oConn.Close();
            }
            catch (SqlException ex)
            {
                if (oConn.State == ConnectionState.Open)
                    oConn.Close();
                throw new Exception(ex.Message);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            for (int i = 0; i < ds.Tables.Count; i++)
            {
                ds.Tables[i].TableName = string.Format("{0}_Table{1}", sProcName, i);
            }
            return ds;
        }
        public Hashtable ExecuteProcedureReturnHashtable(string sProcName, IDictionary parameterCollection)
        {
            return ExecuteProcedureReturnHashtable(sProcName, parameterCollection, 0);
        }
        public Hashtable ExecuteProcedureReturnHashtable(string sProcName, IDictionary parameterCollection, int timeOut)
        {
            Hashtable oReturn = null;
            try
            {
                oConn.Open();
                using (SqlCommand cmd = BuildSqlCommand(sProcName, parameterCollection, timeOut))
                {

                    SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    int nFieldCount = dr.FieldCount;
                    DataTable dtSchema = dr.GetSchemaTable();

                    int counter = 0;
                    while (dr.Read())
                    {
                        if (counter == 0)
                        {
                            if (oReturn == null)
                                oReturn = new Hashtable();
                            for (int i = 0; i < nFieldCount; i++)
                            {
                                string sColName = dr.GetName(i).ToString();
                                oReturn.Add(sColName, dr[sColName]);
                            }
                            counter++;
                        }
                        else
                            break;
                    }
                    dr.Close();
                }
                oConn.Close();
            }
            catch (SqlException ex)
            {
                if (oConn.State == ConnectionState.Open)
                    oConn.Close();
                throw new Exception(ex.Message);
            }
            catch (Exception ex)
            {
                if (oConn.State == ConnectionState.Open)
                    oConn.Close();
                throw new Exception(ex.Message);
            }
            return oReturn;
        }
        public void ExecuteNonQuery(string sProcName, IDictionary parameterCollection)
        {
            ExecuteNonQuery(sProcName, parameterCollection, 0);
        }
        public void ExecuteNonQuery(string sProcName, IDictionary parameterCollection, int timeOut)
        {
            try
            {
                oConn.Open();
                using (SqlCommand cmd = BuildSqlCommand(sProcName, parameterCollection, timeOut))
                {
                    cmd.ExecuteNonQuery();
                }
                oConn.Close();
            }
            catch (SqlException ex)
            {
                if (oConn.State == ConnectionState.Open)
                    oConn.Close();
                throw new Exception(ex.Message);
            }
            catch (Exception ex)
            {
                if (oConn.State == ConnectionState.Open)
                    oConn.Close();
                throw new Exception(ex.Message);
            }
        }

        private SqlCommand BuildSqlCommand(string sProcName, IDictionary ParameterCollection, int nTimeOut)
        {
            string sFieldName = "";
            SqlCommand oCmd = null;
            try
            {
                oCmd = new SqlCommand(sProcName, this.oConn);
                oCmd.CommandType = CommandType.StoredProcedure;
                oCmd.CommandTimeout = (nTimeOut >= 0) ? nTimeOut : oConn.ConnectionTimeout;

                if (oConn.State == ConnectionState.Closed)
                    oConn.Open();
                SqlCommandBuilder.DeriveParameters(oCmd);
                foreach (SqlParameter oParam in oCmd.Parameters)
                {
                    if (oParam.Direction != ParameterDirection.ReturnValue)
                    {
                        sFieldName = oParam.ParameterName.Substring(1);

                        if (ParameterCollection.Contains(sFieldName) && (ParameterCollection[sFieldName] != null))
                        {
                            oParam.Value = ParameterCollection[sFieldName];
                        }
                        sFieldName = "";
                    }
                }
            }
            catch (SqlException ex)
            {
                if (oCmd != null)
                    oCmd.Dispose();
                throw new Exception(ex.Message);
            }
            catch (Exception ex)
            {
                if (oCmd != null)
                    oCmd.Dispose();
                throw new Exception(ex.Message);
            }
            return oCmd;
        }

        #region IDisposable Members

        private void Dispose(bool disposing)
        {
            if (!disposed)
            {
                if (disposing)
                {
                    if (oConn.State == ConnectionState.Open)
                        oConn.Close();
                    oConn.Dispose();
                }
                disposed = true;
            }
        }
        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }
        ~SqlDataService()
        {
            Dispose(false);
        }

        #endregion
    }
}
